package com.cpi.cornpp.instructionsattachment.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.instructionsattachment.dto.InstructionAttachmentDTO;
import com.cpi.cornpp.instructionsattachment.exception.InstructionAttachmentException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class InstructionAttachmentDAO extends DBDAO{
	
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	
	/** SQL List Instruction Attachment*/
	private static final String SELECT_INSTRUCTION_ATTACHMENTS = "SELECT instruction_attachment_id, purchase_instruction_detail_id, description, path FROM INSTRUCTIONS_ATTACHMENTS WHERE purchase_instruction_detail_id = ? ";//$NON-NLS-1$
	
	/** SQL List Instruction Attachment*/
	private static final String SELECT_INSTRUCTION_ATTACHMENT = "SELECT instruction_attachment_id, purchase_instruction_detail_id, description, path FROM INSTRUCTIONS_ATTACHMENTS WHERE instruction_attachment_id = ? ";//$NON-NLS-1$
	
	/** SQL Insert Instruction Attachment*/ 
	private static final String SQL_INSERT_INSTRUCTION_ATTACHMENTS= "INSERT INTO INSTRUCTIONS_ATTACHMENTS (purchase_instruction_detail_id, description, path ) VALUES (?,?,?)";//$NON-NLS-1$

	/** SQL delete Instruction Attachment*/ 
	private static final String SQL_DELETE_INSTRUCTION_ATTACHMENT= "DELETE FROM INSTRUCTIONS_ATTACHMENTS  WHERE instruction_attachment_id = ? ";//$NON-NLS-1$

	/** SQL update Instruction Attachment*/	
	private static final String UPDATE_INSTRUCTION_ATTACHMENT = "UPDATE INSTRUCTIONS_ATTACHMENTS SET description = ? WHERE instruction_attachment_id = ?";//$NON-NLS-1$AMOUNT = ?, OUT_OF_CREDIT = ?, TAX1 = ?, TAX2 = ? WHERE BALANCE_VERSION_ID = ?";//$NON-NLS-1$
	
	
	/**
	 * Devuelve la lista de attachements en base a un Purchase Instruction Detail Id
	 * @author Edgar Joao
	 * @param purchaseInstructionDetailID
	 * @return
	 * @throws InstructionAttachmentException
	 */
	public List getInstructionAttachement( int purchaseInstructionDetailID) throws InstructionAttachmentException{
		List listOfInstructionAttachement = null;
		PreparedStatement preparedStatement = null;
		InstructionAttachmentDTO instructionsAttachmentDTO = null;
		StringBuffer sql = null;
		ResultSet rs = null;
		try{
			
			sql = new StringBuffer(0);
			sql.append(SELECT_INSTRUCTION_ATTACHMENTS);			
			listOfInstructionAttachement = new ArrayList(0);			
			preparedStatement = prepareSQL(sql.toString(), DATASOURCE);			
			preparedStatement.setInt(1, purchaseInstructionDetailID);
			
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				instructionsAttachmentDTO  = new InstructionAttachmentDTO();
				instructionsAttachmentDTO.setInstructionAttachmentId(rs.getInt(1));
				instructionsAttachmentDTO.setPurchaseInstructionDetailId(rs.getInt(2));
				instructionsAttachmentDTO.setDescription(rs.getString(3));
				instructionsAttachmentDTO.setPath(rs.getString(4));
				listOfInstructionAttachement.add(instructionsAttachmentDTO);
			}	
		}catch (PoolException poolException){
			InstructionAttachmentException instructionAttachmentException = null;
			poolException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionAttachmentException;	
		} catch (SQLException sqlException) {
			InstructionAttachmentException instructionAttachmentException = null;
			sqlException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionAttachmentException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfInstructionAttachement;		
	}
	
	
	/**
	 * Devuelve la lista de attachements en base a un Purchase Instruction Detail Id
	 * @author Edgar Joao
	 * @param purchaseInstructionDetailID
	 * @return
	 * @throws InstructionAttachmentException
	 */
	public InstructionAttachmentDTO getSingleInstructionAttachement( int instructionAttachmentId) throws InstructionAttachmentException{			
		PreparedStatement preparedStatement = null;
		InstructionAttachmentDTO instructionsAttachmentDTO = null;
		StringBuffer sql = null;
		ResultSet rs = null;
		try{
			
			sql = new StringBuffer(0);
			sql.append(SELECT_INSTRUCTION_ATTACHMENT);						
			preparedStatement = prepareSQL(sql.toString(), DATASOURCE);			
			preparedStatement.setInt(1, instructionAttachmentId);
			
			rs = executeQuery(preparedStatement);			
			if(rs.next()){
				instructionsAttachmentDTO  = new InstructionAttachmentDTO();
				instructionsAttachmentDTO.setInstructionAttachmentId(rs.getInt(1));
				instructionsAttachmentDTO.setPurchaseInstructionDetailId(rs.getInt(2));
				instructionsAttachmentDTO.setDescription(rs.getString(3));
				instructionsAttachmentDTO.setPath(rs.getString(4));				
			}	
		}catch (PoolException poolException){
			InstructionAttachmentException instructionAttachmentException = null;
			poolException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionAttachmentException;	
		} catch (SQLException sqlException) {
			InstructionAttachmentException instructionAttachmentException = null;
			sqlException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionAttachmentException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return instructionsAttachmentDTO;		
	}


	
	/**
	 * Inserta un instruction forecast
	 * @param dto
	 */
	public void insertInstructionAttachement(InstructionAttachmentDTO dto) throws InstructionAttachmentException {
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL( SQL_INSERT_INSTRUCTION_ATTACHMENTS , DATASOURCE);			
			preparedStatement.setInt(1, dto.getPurchaseInstructionDetailId());
			preparedStatement.setString(2, dto.getDescription());
			preparedStatement.setString(3, dto.getPath());
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				InstructionAttachmentException instructionAttachmentException = null;
				instructionAttachmentException = new InstructionAttachmentException (MSG_NO_INSERT,InstructionAttachmentException.LAYER_DAO,InstructionAttachmentException.ACTION_INSERT);				
				throw instructionAttachmentException;
			}		
		}catch (PoolException poe) {
			InstructionAttachmentException ife = new InstructionAttachmentException(poe, InstructionAttachmentException.LAYER_DAO, InstructionAttachmentException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;
		} catch (SQLException sqle){
			InstructionAttachmentException ife = new InstructionAttachmentException(sqle, InstructionAttachmentException.LAYER_DAO, InstructionAttachmentException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ife;
		} finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqle){
			}
		}	
	}
	
	/**
	 * Borra un instruction forecast
	 * @param instructionForecastId
	 * @throws InstructionAttachementException
	 */
	public void deleteInstructionsView(int instructionAttachmentId)throws InstructionAttachmentException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_DELETE_INSTRUCTION_ATTACHMENT, DATASOURCE);
			pstm.setInt(1, instructionAttachmentId);			
			executeUpdate(pstm);			
		}catch (PoolException poolException){
			InstructionAttachmentException instructionAttachmentException = null;
			poolException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException (poolException, InstructionAttachmentException.LAYER_DAO,InstructionAttachmentException.ACTION_DELETE);
			throw instructionAttachmentException;
		} catch(SQLException sqlException){
			InstructionAttachmentException instructionsForecastsException = null;
			sqlException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionAttachmentException (sqlException, InstructionAttachmentException.LAYER_DAO,InstructionAttachmentException.ACTION_DELETE);
			throw instructionsForecastsException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}

	/**
	 * 
	 * @param dto
	 * @throws InstructionAttachmentException
	 */
	public void updateInstructionAttachment(InstructionAttachmentDTO dto) throws InstructionAttachmentException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(UPDATE_INSTRUCTION_ATTACHMENT, DATASOURCE);
			preparedStatement.setString(1, dto.getDescription());
			preparedStatement.setInt(2, dto.getInstructionAttachmentId());			
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			InstructionAttachmentException exception = null;
			poolException.printStackTrace(System.out);
			exception = new InstructionAttachmentException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw exception;	
		} catch (SQLException sqlException) {
			InstructionAttachmentException instructionAttachmentException = null;
			sqlException.printStackTrace(System.out);
			instructionAttachmentException = new InstructionAttachmentException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw instructionAttachmentException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}

}
